package com.ph.irp.base.assistantmaterial.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.assistantmaterial.dao.PBaseAssistantmaterialCustomerTypeDAO;
import com.ph.irp.model.PBaseAssistantmaterialCustomercategory;
import com.ph.util.DBUtil;

public class PBaseAssistantmaterialCustomerTypeDAOImpl implements
		PBaseAssistantmaterialCustomerTypeDAO {

	/**
	 * 
	 */
	private static final long serialVersionUID = -199933692266551731L;
	// 数据源
	private ComboPooledDataSource dataSource;

	// setter for dataSource
	public void setDataSource(ComboPooledDataSource dataSource) {

		this.dataSource = dataSource;

	}

	/**
	 * 新增客户类别
	 */
	@Override
	public void addCustomerType(
			PBaseAssistantmaterialCustomercategory customerTypeModel)
			throws Throwable {
		// TODO Auto-generated method stub
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;
		// 新增sql语句
		StringBuilder sql = new StringBuilder();

		sql
				.append("insert into p_base_assistantmaterial_customercategory(CustomerCategoryCode,CustomerCategoryName) ");
		sql.append("values('");
		sql.append(customerTypeModel.getCustomerCategoryCode() + "','");
		sql.append(customerTypeModel.getCustomerCategoryName() + "')");
		try {

			con = this.dataSource.getConnection();

			statement = con.prepareStatement(sql.toString());

			statement.executeUpdate();

		} catch (Exception e) {

			throw new Exception(e.getMessage());

		} finally {// 释放资源，关闭连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {

				throw new Exception(e.getMessage());

			}
		}
	}

	/**
	 * 根据客户类别id删除客户类别
	 */
	@Override
	public void deleteCustomer(String customerTypeId) throws Throwable {
		// TODO Auto-generated method stub
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;
		// 查询语句
		String sql = "delete from p_base_assistantmaterial_customercategory where CustomerCategoryID=?";

		try {

			con = this.dataSource.getConnection();

			statement = con.prepareStatement(sql);

			statement.setInt(1, Integer.valueOf(customerTypeId));

			statement.execute();

		} catch (Exception e) {

			throw new Exception(e.getMessage());

		} finally {// 释放资源，关闭连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {

				throw new Exception(e.getMessage());

			}
		}
	}

	/**
	 * 查询所有的客户类别
	 */
	@Override
	public List<PBaseAssistantmaterialCustomercategory> getAllCustomercategorys()
			throws Throwable {
		// TODO Auto-generated method stub
		// 领料用途列表
		List<PBaseAssistantmaterialCustomercategory> cusomerTypeList = new ArrayList<PBaseAssistantmaterialCustomercategory>();
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;
		// 查询语句
		String sql = "select * from p_base_assistantmaterial_customercategory ";

		try {

			con = this.dataSource.getConnection();

			statement = con.prepareStatement(sql);

			resultSet = statement.executeQuery();
			// 处理结果集中的记录到采购价格model
			while (resultSet.next()) {
				PBaseAssistantmaterialCustomercategory cusomerTypeModel = new PBaseAssistantmaterialCustomercategory();

				cusomerTypeModel.setCustomerCategoryId(resultSet.getInt(1));

				cusomerTypeModel
						.setCustomerCategoryCode(resultSet.getString(2));

				cusomerTypeModel
						.setCustomerCategoryName(resultSet.getString(3));

				cusomerTypeList.add(cusomerTypeModel);

			}

		} catch (Exception e) {

			throw new Exception(e.getMessage());

		} finally {// 释放资源，关闭连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {

				throw new Exception(e.getMessage());

			}
		}
		return cusomerTypeList;
	}

	/**
	 * 根据code模糊查询客户类别
	 */
	@Override
	public List<PBaseAssistantmaterialCustomercategory> getCustomerTypeByCode(
			String customerTypeCode) throws Throwable {
		// TODO Auto-generated method stub
		// 领料用途列表
		List<PBaseAssistantmaterialCustomercategory> cusomerTypeList = new ArrayList<PBaseAssistantmaterialCustomercategory>();
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;
		// 查询语句
		String sql = "select * from p_base_assistantmaterial_customercategory where CustomerCategoryCode like ?";

		try {

			con = this.dataSource.getConnection();

			statement = con.prepareStatement(sql);

			statement.setString(1, "%" + customerTypeCode + "%");

			resultSet = statement.executeQuery();
			// 处理结果集中的记录到采购价格model
			while (resultSet.next()) {
				PBaseAssistantmaterialCustomercategory cusomerTypeModel = new PBaseAssistantmaterialCustomercategory();

				cusomerTypeModel.setCustomerCategoryId(resultSet.getInt(1));

				cusomerTypeModel
						.setCustomerCategoryCode(resultSet.getString(2));

				cusomerTypeModel
						.setCustomerCategoryName(resultSet.getString(3));

				cusomerTypeList.add(cusomerTypeModel);

			}

		} catch (Exception e) {

			throw new Exception(e.getMessage());

		} finally {// 释放资源，关闭连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {

				throw new Exception(e.getMessage());

			}
		}
		return cusomerTypeList;
	}

	/**
	 * 根据id查询客户类别
	 */
	@Override
	public PBaseAssistantmaterialCustomercategory getCustomerTypeById(
			Integer customerTypeId) throws Throwable {
		// TODO Auto-generated method stub
		// 辅助物料客户分类model
		PBaseAssistantmaterialCustomercategory cusomerTypeModel = new PBaseAssistantmaterialCustomercategory();
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;
		// 查询语句
		String sql = "select * from p_base_assistantmaterial_customercategory where  CustomerCategoryID=?";

		try {
			// 得到数据库连接
			con = this.dataSource.getConnection();
			//
			statement = con.prepareStatement(sql);
			// 绑定宿主变量
			statement.setInt(1, customerTypeId);
			// 执行查询，得到结果集
			resultSet = statement.executeQuery();
			// 处理结果集中的记录到采购价格model
			if (resultSet.next()) {

				cusomerTypeModel.setCustomerCategoryId(resultSet.getInt(1));

				cusomerTypeModel
						.setCustomerCategoryCode(resultSet.getString(2));

				cusomerTypeModel
						.setCustomerCategoryName(resultSet.getString(3));

			}

		} catch (Exception e) {

			throw new Exception(e.getMessage());

		} finally {// 释放资源，关闭连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {

				throw new Exception(e.getMessage());

			}
		}
		return cusomerTypeModel;
	}

	/**
	 * 根据name模糊查询客户类别
	 */
	@Override
	public List<PBaseAssistantmaterialCustomercategory> getCustomerTypeByName(
			String customerTypeName) throws Throwable {
		// TODO Auto-generated method stub
		// 领料用途列表
		List<PBaseAssistantmaterialCustomercategory> cusomerTypeList = new ArrayList<PBaseAssistantmaterialCustomercategory>();
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;
		// 查询语句
		String sql = "select * from p_base_assistantmaterial_customercategory where CustomerCategoryName like ?";

		try {
			// 得到数据库连接
			con = this.dataSource.getConnection();
			//
			statement = con.prepareStatement(sql);
			// 绑定宿主变量
			statement.setString(1, "%" + customerTypeName + "%");
			// 执行查询，得到结果集
			resultSet = statement.executeQuery();
			// 处理结果集中的记录到采购价格model
			while (resultSet.next()) {
				PBaseAssistantmaterialCustomercategory cusomerTypeModel = new PBaseAssistantmaterialCustomercategory();

				cusomerTypeModel.setCustomerCategoryId(resultSet.getInt(1));

				cusomerTypeModel
						.setCustomerCategoryCode(resultSet.getString(2));

				cusomerTypeModel
						.setCustomerCategoryName(resultSet.getString(3));

				cusomerTypeList.add(cusomerTypeModel);

			}

		} catch (Exception e) {

			throw new Exception(e.getMessage());

		} finally {// 释放资源，关闭连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {

				throw new Exception(e.getMessage());

			}
		}
		return cusomerTypeList;
	}

	/**
	 * 根据name和code查询客户类别
	 */
	@Override
	public List<PBaseAssistantmaterialCustomercategory> getCustomerTypeByNameAndCode(
			String customerTypeName, String customerTypeCode) throws Throwable {
		// TODO Auto-generated method stub
		// 领料用途列表
		List<PBaseAssistantmaterialCustomercategory> cusomerTypeList = new ArrayList<PBaseAssistantmaterialCustomercategory>();
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;
		// 查询语句
		String sql = "select * from p_base_assistantmaterial_customercategory where CustomerCategoryName like ? or CustomerCategoryName like ?";

		try {
			// 得到数据库连接
			con = this.dataSource.getConnection();
			//
			statement = con.prepareStatement(sql);
			// 绑定宿主变量
			statement.setString(1, "%" + customerTypeName + "%");
			statement.setString(2, "%" + customerTypeCode + "%");
			// 执行查询，得到结果集
			resultSet = statement.executeQuery();
			// 处理结果集中的记录到采购价格model
			while (resultSet.next()) {
				PBaseAssistantmaterialCustomercategory cusomerTypeModel = new PBaseAssistantmaterialCustomercategory();

				cusomerTypeModel.setCustomerCategoryId(resultSet.getInt(1));

				cusomerTypeModel
						.setCustomerCategoryCode(resultSet.getString(2));

				cusomerTypeModel
						.setCustomerCategoryName(resultSet.getString(3));

				cusomerTypeList.add(cusomerTypeModel);

			}

		} catch (Exception e) {

			throw new Exception(e.getMessage());

		} finally {// 释放资源，关闭连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {

				throw new Exception(e.getMessage());

			}
		}
		return cusomerTypeList;
	}

	/**
	 * 更新客户类别
	 */
	@Override
	public void updateCustomerType(
			PBaseAssistantmaterialCustomercategory customerTypeModel)
			throws Throwable {
		// TODO Auto-generated method stub
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 修改sql语句
		StringBuilder sql = new StringBuilder();

		sql
				.append("update p_base_assistantmaterial_customercategory set CustomerCategoryCode='");
		sql.append(customerTypeModel.getCustomerCategoryCode() + "',");
		sql.append("CustomerCategoryName='"
				+ customerTypeModel.getCustomerCategoryName());
		sql.append("' where CustomerCategoryId="
				+ customerTypeModel.getCustomerCategoryId());
		try {

			con = this.dataSource.getConnection();

			statement = con.prepareStatement(sql.toString());
			statement.executeUpdate();

		} catch (Exception e) {

			throw new Exception(e.getMessage());

		} finally {// 释放资源，关闭连接

			try {
				DBUtil.setNullObject(con, statement, null);
			} catch (Exception e) {

				throw new Exception(e.getMessage());

			}
		}

	}

}
